﻿using System;
using System.Collections.Generic;
using System.Text;
using Oracle.DataAccess.Client;
using System.Data;
using System.Data.Common;
using MYTech.Data;
using MYTech.Utils;
using MYTech.Data.Oracle;

namespace MYTech.Oracle
{
    public enum SessionOption
    {
        ParallelDDL = 1,
        ParallelDML = 2,
        ParallelQuery = 4
    }

    public class DataHelper
    {
        public static string ConnectionString;

        private static OracleConnection CurrentConn;

        public static void SetConnection(string ip, int port, string dataBaseName, string username, string password)
        {
            DBConnection dbconn = new DBConnection(DBType.Oracle, ip, dataBaseName, port, username, password);
            ConnectionString = dbconn.DBConnectionString;
        }

        public static void SetConnection(Connect connect)
        {
            ConnectionString = connect.ConnStr;
        }

        public static void Close()
        {
            if (CurrentConn != null && CurrentConn.State == ConnectionState.Open)
            {
                CurrentConn.Close();
            }
        }

        public static void Close(OracleConnection conn)
        {
            if (conn != null && conn.State == ConnectionState.Open)
            {
                conn.Close();
            }
        }

        /// <summary>
        /// 测试数据库连接，成功时返回为null
        /// </summary>
        /// <returns></returns>
        public static string Test()
        {
            OracleConnection conn = new OracleConnection(ConnectionString);
            try
            {
                conn.Open();
            }
            catch (Exception ex)
            {
                return ex.ToString();
            }
            finally
            {
                conn.Close();
            }
            return null;
        }

        public static OracleConnection Open()
        {
            OracleConnection conn = new OracleConnection(ConnectionString);
            if (conn.State == ConnectionState.Closed)
                conn.Open();
            CurrentConn = conn;
            return conn;
        }

        public static OracleConnection Open(OracleConnection conn)
        {
            if (conn.State == ConnectionState.Closed)
                conn.Open();
            CurrentConn = conn;
            return conn;
        }

        public static OracleConnection Open(string connStr)
        {
            OracleConnection conn = new OracleConnection(connStr);
            if (conn.State == ConnectionState.Closed)
                conn.Open();
            CurrentConn = conn;
            return conn;
        }

        /// <summary>
        /// 判断是否存在数据表
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public static bool TableExist(string tableName)
        {
            if (tableName.IndexOf('@') >= 0)
                return true;
            string sql = string.Format("select 1 from user_tables where table_name='{0}' and rownum=1", tableName.ToUpper());
            return HasRow(sql);
        }

        /// <summary>
        /// 判断是否存在匹配的表
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public static bool TableLike(string tableName)
        {
            string sql = string.Format("select 1 from user_tables where table_name like '{0}' escape '\\' and rownum=1", tableName.ToUpper());
            return HasRow(sql);
        }

        /// <summary>
        /// 判断是否存在字段
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="fieldName"></param>
        /// <returns></returns>
        public static bool FieldExist(string tableName, string fieldName)
        {
            if (tableName.IndexOf('@') >= 0)
                return true;
            string sql = string.Format("select 1 from USER_TAB_COLUMNS where table_name='{0}' and column_name='{0}' and rownum=1", tableName.ToUpper(), fieldName.ToUpper());
            return HasRow(sql);
        }

        /// <summary>
        /// 判断是否存在序列
        /// </summary>
        /// <param name="sequence"></param>
        /// <returns></returns>
        public static bool SequenceExist(string sequence)
        {
            string sql = string.Format("select 1 from USER_SEQUENCES where SEQUENCE_NAME='{0}' and rownum=1", sequence.ToUpper());
            return HasRow(sql);
        }

        /// <summary>
        /// 判断是否存在分区
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="partitionName"></param>
        /// <returns></returns>
        public static bool PartitionExist(string tableName, string partitionName)
        {
            if (tableName.IndexOf('@') >= 0) // No support for dblink
                return true;
            string sql = string.Format("select 1 from USER_TAB_PARTITIONS where partition_name='{0}' and table_name='{1}' and rownum=1", partitionName.ToUpper(), tableName.ToUpper());
            return HasRow(sql);
        }

        /// <summary>
        /// 判断索引是否存在
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="indexName"></param>
        /// <returns></returns>
        public static bool IndexExist(string tableName, string indexName)
        {
            if (tableName.IndexOf('@') >= 0) // No support for dblink
                return true;
            string sql = string.Format("select 1 from all_indexes where index_name='{0}' and table_name='{1}' and rownum=1", indexName.ToUpper(), tableName.ToUpper());
            return HasRow(sql);
        }

        public static bool HasRow(string sqlStr)
        {
            return HasRow(sqlStr, null);
        }

        public static bool HasRow(string sqlStr, object param)
        {
            using (OracleCommand cmd = GetCommand(sqlStr, param))
            using (OracleDataReader dr = cmd.ExecuteReader())
                return dr.Read();
        }

        internal static OracleCommand GetCommand(string sqlStr)
        {
            OracleConnection conn = Open();
            return new OracleCommand(sqlStr, conn);
        }

        internal static OracleCommand GetCommand(string sqlStr, object param)
        {
            OracleConnection conn = Open();
            OracleCommand cmd = new OracleCommand(sqlStr, conn);
            OracleParameter parameter = new OracleParameter { ParameterName = "0", Value = param ?? "" };
            cmd.Parameters.Add(parameter);
            return cmd;
        }

        internal static OracleCommand GetCommand(string sqlStr, OracleParameter param)
        {
            OracleConnection conn = Open();
            OracleCommand cmd = new OracleCommand(sqlStr, conn);
            cmd.Parameters.Add(param);
            return cmd;
        }

        internal static OracleCommand GetCommand(string sqlStr, object[] param)
        {
            OracleConnection conn = Open();
            OracleCommand cmd = new OracleCommand(sqlStr, conn);
            int count = param.Length;
            for (int i = 0; i < count; ++i)
            {
                OracleParameter parameter = new OracleParameter { ParameterName = i.ToString(), Value = param[i] ?? "" };
                cmd.Parameters.Add(parameter);
            }
            return cmd;
        }

        internal static OracleCommand GetCommand(string sqlStr, OracleParameter[] parameters)
        {
            OracleConnection conn = Open();
            OracleCommand cmd = new OracleCommand(sqlStr, conn);
            int count = parameters.Length;
            foreach (OracleParameter param in parameters)
            {
                cmd.Parameters.Add(param);
            }
            return cmd;
        }

        internal static OracleCommand GetCommand(string sqlStr, int paramcount)
        {
            OracleConnection conn = Open();
            OracleCommand cmd = new OracleCommand(sqlStr, conn);
            for (int i = 0; i < paramcount; ++i)
            {
                OracleParameter parameter = new OracleParameter { ParameterName = i.ToString() };
                cmd.Parameters.Add(parameter);
            }
            return cmd;
        }

        public static string GetPagedSQL(string sqlStr, string sqlOrder, int startRecord, int maxRecords)
        {
            return GetPagedSQL(false, sqlStr, sqlOrder, startRecord, maxRecords);
        }

        public static string GetPagedSQL(bool singleSQL, string sqlStr, string sqlOrder, int startRecord, int maxRecords)
        {
            if (sqlOrder == null || singleSQL)
            {
                if (startRecord == 0)
                    return string.Format("SELECT t.* FROM({0}{2}) t WHERE ROWNUM<={1}", sqlStr, maxRecords, sqlOrder);
                return string.Format("SELECT * FROM (SELECT t.*,ROWNUM num_ FROM({0}{3}) t WHERE ROWNUM<={1}) where num_>{2}", sqlStr, startRecord + maxRecords, startRecord, sqlOrder);
            }
            if (startRecord == 0)
                return string.Format("SELECT t.* FROM(SELECT * FROM ({0}){2}) t WHERE ROWNUM<={1}", sqlStr, maxRecords, sqlOrder);
            return string.Format("SELECT * FROM (SELECT t.*,ROWNUM num_ FROM(SELECT * FROM ({0}){3}) t WHERE ROWNUM<={1}) where num_>{2}", sqlStr, startRecord + maxRecords, startRecord, sqlOrder);
        }

        public static List<string> GetFields(string tableName)
        {
            if (tableName.IndexOf('@') >= 0)
                return null;
            List<string> fields = new List<string>();
            string sql = string.Format("select column_name from USER_TAB_COLUMNS where table_name='{0}'", tableName.ToUpper());
            ExecuteNonQuery(sql, dataReader =>
            {
                fields.Add(dataReader[0].ToString());
                return true;
            });
            return fields;
        }

        public static DataTable GetDataTableBySql(string sqlStr, string sqlOrder, int startRecord, int maxRecords, ReportProgressConut updateRowCountText)
        {
            return GetDataTableBySql(GetPagedSQL(sqlStr, sqlOrder, startRecord, maxRecords), updateRowCountText);
        }

        public static DataTable GetDataTableBySql(string sqlStr, List<string> stringColumns, ReportProgressConut updateRowCountText)
        {
            using (OracleCommand cmd = GetCommand(sqlStr))
            using (OracleDataReader dr = cmd.ExecuteReader())
            {
                return GetDataTable(dr, stringColumns, updateRowCountText);
            }
        }

        public static DataTable GetDataTableBySql(string sqlStr, ReportProgressConut updateRowCountText)
        {
            using (OracleCommand cmd = GetCommand(sqlStr))
            using (OracleDataReader dr = cmd.ExecuteReader())
            {
                return GetDataTable(dr, null, updateRowCountText);
            }
        }

        public static DataTable GetDataTableBySql(string sqlStr)
        {
            return GetDataTableBySql(sqlStr, null);
        }

        public static DataTable GetDataTableBySql(string sqlStr, CommandType commandType, OracleParameter[] parameters)
        {
            using (OracleCommand command = GetCommand(sqlStr))
            {
                DataTable data = new DataTable();
                command.CommandType = commandType;
                if (parameters != null)
                {
                    foreach (OracleParameter parameter in parameters)
                    {
                        command.Parameters.Add(parameter);
                    }
                }
                OracleDataAdapter adapter = new OracleDataAdapter(command);
                adapter.Fill(data);
                return data;
            }
        }

        public static DataTable GetDataTableBySql(string sqlStr, CommandType commandType)
        {
            return GetDataTableBySql(sqlStr, commandType, null);
        }

        private static DataTable GetDataTable(OracleDataReader dr, List<string> stringColumns, ReportProgressConut updateRowCountText)
        {
            DataTable dt = new DataTable();
            int i = 0;
            int fieldCount = dr.FieldCount - 1; // 屏蔽最后一列NUM_
            if (dr.GetName(fieldCount) != "NUM_")
                ++fieldCount;
            for (; i < fieldCount; i++)
            {
                string columnName = dr.GetName(i);
                dt.Columns.Add(columnName, stringColumns == null || !stringColumns.Contains(columnName) ? dr.GetFieldType(i) : typeof(string));
            }
            i = 0;
            object[] t = new object[fieldCount];
            while (dr.Read())
            {
                dr.GetValues(t);
                dt.Rows.Add(t);
                i++;
                if (i % 100 == 0 && updateRowCountText != null && !updateRowCountText(i))
                    break;
            }
            return dt;
        }

        /// <summary>
        /// 执行SQL语句
        /// </summary>
        /// <param name="sqlStr">执行的sql语句</param>
        /// <returns>返回受影响行数</returns>
        public static int ExecuteNonQuery(string sqlStr)
        {
            using (OracleCommand cmd = GetCommand(sqlStr))
            {
                return cmd.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// 执行SQL语句
        /// </summary>
        /// <param name="conn">数据库的连接</param>
        /// <param name="sqlStr">执行的sql语句</param>
        /// <returns>返回受影响行数</returns>
        public static int ExecuteNonQuery(string sqlStr, OracleConnection conn)
        {
            using (OracleCommand cmd = new OracleCommand(sqlStr, Open(conn)))
            {
                return cmd.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// 执行SQL语句
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="sqlStr">执行的sql语句</param>
        /// <returns>返回受影响行数</returns>
        public static int ExecuteNonQuery(string connectionString, string sqlStr)
        {
            using (OracleCommand cmd = new OracleCommand(sqlStr, Open(connectionString)))
            {
                return cmd.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// 对数据库执行增删改操作
        /// </summary>
        /// <param name="sqlStr">要执行的SQL语句</param>
        /// <param name="commandType">要执行的查询语句的类型，如存储过程或者SQL文本命令</param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string sqlStr, CommandType commandType)
        {
            return ExecuteNonQuery(sqlStr, commandType, null);
        }

        /// <summary>
        /// 对数据库执行增删改操作
        /// </summary>
        /// <param name="sqlStr">要执行的SQL语句</param>
        /// <param name="parameters">Transact-SQL 语句或存储过程的参数数组</param>
        /// <returns>返回执行操作受影响的行数</returns>
        public static int ExecuteNonQuery(string sqlStr, OracleParameter[] parameters)
        {
            return ExecuteNonQuery(sqlStr, CommandType.Text, parameters);
        }

        /// <summary>
        /// 对数据库执行增删改操作
        /// </summary>
        /// <param name="sqlStr">要执行的SQL语句</param>
        /// <param name="commandType">要执行的查询语句的类型，如存储过程或者SQL文本命令</param>
        /// <param name="parameters">Transact-SQL 语句或存储过程的参数数组</param>
        /// <returns>返回执行操作受影响的行数</returns>
        public static int ExecuteNonQuery(string sqlStr, CommandType commandType, OracleParameter[] parameters)
        {
            using (OracleCommand command = GetCommand(sqlStr))
            {
                command.CommandType = commandType;
                if (parameters != null)
                {
                    foreach (OracleParameter parameter in parameters)
                    {
                        command.Parameters.Add(parameter);
                    }
                }
                return command.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// 执行SQL语句
        /// </summary>
        /// <param name="sqlStr">执行的sql语句</param>
        /// <param name="onRead"></param>
        public static void ExecuteNonQuery(string sqlStr, OnRead onRead)
        {
            using (OracleCommand cmd = GetCommand(sqlStr))
            using (OracleDataReader dr = cmd.ExecuteReader())
            {
                while (dr.Read())
                {
                    if (!onRead(dr))
                        break;
                }
            }
        }

        /// <summary>
        /// 执行SQL语句
        /// </summary>
        /// <param name="sqlStr">执行的sql语句</param>
        /// <param name="param"></param>
        /// <param name="onRead"></param>
        public static void ExecuteNonQuery(string sqlStr, object param, OnRead onRead)
        {
            using (OracleCommand cmd = GetCommand(sqlStr, param))
            using (OracleDataReader dr = cmd.ExecuteReader())
            {
                while (dr.Read())
                {
                    if (!onRead(dr))
                        break;
                }
            }
        }

        /// <summary>
        /// 执行SQL语句
        /// </summary>
        /// <param name="sqlStr">执行的sql语句</param>
        /// <param name="parameters"></param>
        /// <param name="onRead"></param>
        public static void ExecuteNonQuery(string sqlStr, object[] parameters, OnRead onRead)
        {
            using (OracleCommand cmd = GetCommand(sqlStr, parameters))
            using (OracleDataReader dr = cmd.ExecuteReader())
            {
                while (dr.Read())
                {
                    if (!onRead(dr))
                        break;
                }
            }
        }

        /// <summary>
        /// 执行SQL语句
        /// </summary>
        /// <param name="sqlStr">执行的sql语句</param>
        /// <param name="onExecute"></param>
        /// <param name="onRead"></param>
        public static void ExecuteNonQuery(string sqlStr, OnRead onExecute, OnRead onRead)
        {
            using (OracleCommand cmd = GetCommand(sqlStr))
            using (OracleDataReader dr = cmd.ExecuteReader())
            {
                if (onExecute(dr))
                {
                    while (dr.Read())
                    {
                        if (!onRead(dr))
                            break;
                    }
                }
            }
        }

        /// <summary>
        /// 执行SQL语句
        /// </summary>
        /// <param name="sqlStr">执行的sql语句</param>
        /// <param name="startRecord">起始位置</param>
        /// <param name="maxRecords">最大位置</param>
        /// <param name="onRead"></param>
        public static void ExecuteNonQuery(string sqlStr, string sqlOrder, int startRecord, int maxRecords, OnRead onRead)
        {
            ExecuteNonQuery(GetPagedSQL(sqlStr, sqlOrder, startRecord, maxRecords), onRead);
        }

        /// <summary>
        /// 从数据库中检索单个值
        /// </summary>
        /// <param name="sqlStr">要执行的查询SQL文本命令</param>
        /// <returns></returns>
        public static Object ExecuteScalar(string sqlStr)
        {
            return ExecuteScalar(sqlStr, CommandType.Text, null);
        }

        /// <summary>
        /// 从数据库中检索单个值
        /// </summary>
        /// <param name="sqlStr">要执行的SQL语句</param>
        /// <param name="commandType">要执行的查询语句的类型，如存储过程或者SQL文本命令</param>
        /// <returns></returns>
        public static Object ExecuteScalar(string sqlStr, CommandType commandType)
        {
            return ExecuteScalar(sqlStr, commandType, null);
        }

        /// <summary>
        /// 从数据库中检索单个值
        /// </summary>
        /// <param name="sqlStr">要执行的SQL语句</param>
        /// <param name="commandType">要执行的查询语句的类型，如存储过程或者SQL文本命令</param>
        /// <param name="parameters">Transact-SQL 语句或存储过程的参数数组</param>
        /// <returns></returns>
        public static Object ExecuteScalar(string sqlStr, CommandType commandType, OracleParameter[] parameters)
        {
            using (OracleCommand cmd = GetCommand(sqlStr))
            {
                cmd.CommandType = commandType;
                if (parameters != null)
                {
                    foreach (OracleParameter parameter in parameters)
                    {
                        cmd.Parameters.Add(parameter);
                    }
                }
                return cmd.ExecuteScalar();
            }
        }

        /// <summary>
        /// 获取session ID
        /// </summary>
        /// <returns></returns>
        public static string GetSessionID()
        {
            try
            {
                return ExecuteScalar("select USERENV('SID') from dual").ToString();
            }
            catch
            {
                return null;
            }
        }

        /// <summary>
        /// 强制关闭Session
        /// </summary>
        /// <param name="sid"></param>
        public static void KillSession(string sid)
        {
            if (sid != null)
            {
                try
                {
                    object serial = ExecuteScalar("select serial# from V$session where SID=" + sid);
                    if (serial != null)
                        ExecuteNonQuery(string.Format("alter system kill session '{0},{1}'", sid, serial));
                }
                catch
                {
                }
                finally
                {
                    Close();
                }
            }
        }

        public static void AlterSession(uint sessionOption)
        {
            if ((sessionOption & (uint)SessionOption.ParallelDDL) != 0)
                ExecuteNonQuery("alter session enable parallel ddl");
            if ((sessionOption & (uint)SessionOption.ParallelDML) != 0)
                ExecuteNonQuery("alter session enable parallel dml");
            if ((sessionOption & (uint)SessionOption.ParallelQuery) != 0)
                ExecuteNonQuery("alter session enable parallel query");
        }

        /// <summary>
        /// 清空并删除匹配的表
        /// </summary>
        /// <param name="tableNamePattern"></param>
        public static void TruncateDropTables(string tableNamePattern)
        {
            string sql = string.Format("select table_name from user_tables where table_name like '{0}' escape '\\'", tableNamePattern.ToUpper());
            DataTable dtResult = GetDataTableBySql(sql);
            foreach (DataRow dr in dtResult.Rows)
            {
                TruncateDropTable((string)dr["table_name"]);
            }
        }

        /// <summary>
        /// 清空表
        /// </summary>
        /// <param name="tablename"></param>
        public static void TruncateTable(string tablename)
        {
            ExecuteNonQuery("TRUNCATE TABLE " + tablename);
        }

        /// <summary>
        /// 清空并删除表
        /// </summary>
        /// <param name="tablename"></param>
        public static void TruncateDropTable(string tablename)
        {
            ExecuteNonQuery("TRUNCATE TABLE " + tablename);
            ExecuteNonQuery(string.Format("DROP TABLE {0} PURGE", tablename));
        }

        /// <summary>
        /// 创建索引
        /// </summary>
        /// <param name="type"></param>
        /// <param name="tablename"></param>
        /// <param name="indexname"></param>
        /// <param name="fields"></param>
        /// <param name="hasPartition"></param>
        public static void CreateIndex(string type, string tablename, string indexname, string fields, bool hasPartition)
        {
            ExecuteNonQuery(string.Format("CREATE {0} INDEX {1}_{2} on {1}({3}){4}", type, tablename, indexname, fields, hasPartition ? " local online" : null));
        }

        /// <summary>
        /// 创建索引
        /// </summary>
        /// <param name="tablename">表名</param>
        /// <param name="indexname">索引名</param>
        /// <param name="fields">字段名</param>
        public static void CreateIndex(string tablename, string indexname, string fields)
        {
            CreateIndex(null, tablename, indexname, fields, false);
        }

        /// <summary>
        /// 排序后回填
        /// </summary>
        /// <param name="result"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public static bool BulkCopy(DataTable result, string tableName)
        {
            try
            {
                List<string> fields = GetFields(tableName);
                DataTable dt = DataTableHelper.OrdinalTable(result, fields.ToArray());
                using (OracleBulkCopy bulkCopy = new OracleBulkCopy(Open()) { DestinationTableName = tableName })
                {
                    bulkCopy.WriteToServer(dt);
                }
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        public static void BulkCopy(string tableName, DataRow[] rows)
        {
            using (OracleBulkCopy bulkCopy = new OracleBulkCopy(Open()) { DestinationTableName = tableName })
            {
                bulkCopy.WriteToServer(rows);
            }
        }

        public static void BulkCopy(string tableName, IDataReader reader)
        {
            using (OracleBulkCopy bulkCopy = new OracleBulkCopy(Open()) { DestinationTableName = tableName })
            {
                if (reader.FieldCount > 1)
                {
                    for (int i = 0; i < reader.FieldCount; ++i)
                        bulkCopy.ColumnMappings.Add(i, reader.GetName(i));
                }
                bulkCopy.WriteToServer(reader);
            }
        }

        public static string TimeMonthPartition(string tablename, string fieldname, DateTime beginday, int counts)
        {
            beginday = new DateTime(beginday.Year, beginday.Month, 1);
            string[] strArray = new string[counts];
            for (int i = 0; i < strArray.Length; i++)
                strArray[i] = string.Format("partition {0}_{1:yyMMdd}0000 values less than ({2})", tablename, beginday.AddMonths(i), TimeHelper.ToDateTime(beginday.AddMonths(i + 1)));
            return string.Format("partition by range ({0})({1})", fieldname, string.Join(",", strArray));
        }

        public static string TimeWeekPartition(string tablename, string fieldname, DateTime beginday, int counts)
        {
            beginday = TimeHelper.FirstDayOfWeek(beginday);
            string[] strArray = new string[counts];
            for (int i = 0; i < strArray.Length; i++)
                strArray[i] = string.Format("partition {0}_{1:yyMMdd}0000 values less than ({2})", tablename, beginday.AddDays(i * 7), TimeHelper.ToDateTime(beginday.AddDays((i + 1) * 7)));
            return string.Format("partition by range ({0})({1})", fieldname, string.Join(",", strArray));
        }

        public static string TimeDayPartition(string tablename, string fieldname, DateTime beginday, int counts)
        {
            string[] partitions = new string[counts];
            for (int i = 0; i < partitions.Length; ++i)
                partitions[i] = string.Format("partition {0}_{1:yyMMdd}0000 values less than ({2})", tablename, beginday.AddDays(i), TimeHelper.ToDateTime(beginday.AddDays(i + 1)));
            return string.Format("partition by range ({0})({1})", fieldname, string.Join(",", partitions));
        }

        /// <summary>
        /// 按小时进行分区，默认24小时
        /// </summary>
        /// <param name="tablename"></param>
        /// <param name="fieldname"></param>
        /// <param name="beginHour"></param>
        /// <returns></returns>
        public static string TimeHourPartition(string tablename, string fieldname, DateTime beginHour)
        {
            return TimeHourPartition(tablename, fieldname, beginHour, 24);
        }

        public static string TimeHourPartition(string tablename, string fieldname, DateTime beginHour, int counts)
        {
            string[] partitions = new string[counts];
            for (int i = 0; i < partitions.Length; ++i)
                partitions[i] = string.Format("partition {0}_{1:yyMMddHH}00 values less than ({2})", tablename, beginHour.AddHours(i), TimeHelper.ToDateTime(beginHour.AddHours(i + 1)));
            return string.Format("partition by range ({0})({1})", fieldname, string.Join(",", partitions));
        }

        public static string TimeHourPartition(string tablename, string subPartition, string fieldname, DateTime beginHour)
        {
            string[] partitions = new string[24];
            for (int i = 0; i < partitions.Length; ++i)
                partitions[i] = string.Format("partition {0}_{1:yyMMddHH}00 values less than ({2})", tablename, beginHour.AddHours(i), TimeHelper.ToDateTime(beginHour.AddHours(i + 1)));
            return string.Format("partition by range ({0}){1}({2})", fieldname, subPartition, string.Join(",", partitions));
        }

        public static string HashSubPartition(string fieldname, int count)
        {
            string[] partitions = new string[count];
            for (int i = 0; i < partitions.Length; ++i)
                partitions[i] = "SUBPARTITION h" + i;
            return string.Format("SUBPARTITION BY HASH({0}) SUBPARTITION TEMPLATE({1})", fieldname, string.Join(",", partitions));
        }
    }
}
